package com.xcity.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;

public class StatEmployeeUtils {

	
	static String jdbcUrl = "jdbc:mysql://{0}/{1}?characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true";
	
	static enum Arg {
		DBHOST("rdsd09ffm5143cy22qd4.mysql.rds.aliyuncs.com"),
		DBNAME("city_pro"),
		DBUSER("xtoneapp"),
		DBPWD("Xtone35App16Lb"),
		E(null) // employees
		;
		String value;
		Arg(String value) {
			this.value = value;
		}
		static void fill(String[] args) {
			Map<String, String> argsMap = wrapArgs(args);
			Arg[] vals = values();
			for (Arg arg : vals) {
				String argValue = argsMap.get('-' + arg.name().toLowerCase());
				if (argValue != null) {
					arg.value = argValue;
				}
			}
		}
	}
	
	static final class LOG {
		static void info(String s, Object... args) {
			if (args != null) {
				for (Object o : args) {
					s = s.replaceFirst("\\{}", String.valueOf(o));
				}
			}
			System.out.println(s);
		}
		static void error(Throwable t, String s, Object... args) {
			t.printStackTrace();
			info(s, args);
		}
	}
	
	
	public static void main(String[] args) throws Exception {
		Arg.fill(args);
		String[] ss = Arg.E.value.split(" ");
		if (ss == null || ss.length == 0) {
			LOG.info("[ARG EMPLOYEES EMPTY]");
			return;
		}
		int[] employeeIds = new int[ss.length];
		for (int i = 0; i < ss.length; i++) {
			employeeIds[i] = Integer.parseInt(ss[i]);
		}
		
		String url = MessageFormat.format(jdbcUrl, Arg.DBHOST.value, Arg.DBNAME.value);
		LOG.info("[TRY CONNECT DB]URL[{}]", url);
		Connection conn = null;
		try {
			conn = getConnection(url, Arg.DBUSER.value, Arg.DBPWD.value);
			String sql = "SELECT COUNT(id) AS count FROM employee WHERE templateId = ?";
			for (int id : employeeIds) {
				List<JSONObject> list = query(conn, sql, id);
				LOG.info("{}={}", id, list.isEmpty() ? 0 : list.get(0).get("count"));
			}
		} finally {
			conn.close();
		}
	}
	
	static Connection getConnection(String url, String user, String password) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection(url, user, password);
	}
	
	static List<JSONObject> query(Connection conn, String sql, Object... params) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0, len = params != null ? params.length : 0; i < len; i++) {
				ps.setObject(i + 1, params[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			List<JSONObject> list = new ArrayList<JSONObject>();
			while (rs.next()) {
				JSONObject json = new JSONObject();
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					String label = meta.getColumnLabel(i);
					Object val = rs.getObject(i);
					json.put(label, val);
				}
				list.add(json);
			}
			return list;
		} finally {
			closeQuietly(rs);
			closeQuietly(ps);
		}
	}
	
	static void closeQuietly(AutoCloseable c) {
		if (c != null) {
			try {
				c.close();
			} catch (Exception e) {}
		}
	}
	
	static Map<String, String> wrapArgs(String[] args) {
		Map<String, String> map = new LinkedHashMap<>();
		for (int i = 0; i < args.length; i += 2) {
			String key = args[i];
			String val = args[i + 1];
			map.put(key, val);
		}
		return map;
	}
	
}
